package com.lzb.demo;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.lang.reflect.Field;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.xssf.usermodel.XSSFCell;

import com.lzb.common.util.ReflectUtil;

/**
 * 基于POI实现的Excel工具类--HSSF是POI工程对Excel 97(-2007)文件操作的纯Java实现 
 * 
 * @author liujiduo
 * 
 */
public class HssfExcelHelper extends ExcelHelper {

	public static final SimpleDateFormat format = new SimpleDateFormat();
	private static HssfExcelHelper instance = null; // 单例对象

	private File file; // 操作文件

	/**
	 * 私有化构造方法
	 * 
	 * @param file
	 *            文件对象
	 */
	private HssfExcelHelper(File file) {
		super();
		this.file = file;
	}

	public File getFile() {
		return file;
	}

	public void setFile(File file) {
		this.file = file;
	}

	/**
	 * 获取单例对象并进行初始化
	 * 
	 * @param file
	 *            文件对象
	 * @return 返回初始化后的单例对象
	 */
	public static HssfExcelHelper getInstance(File file) {
		if (instance == null) {
			// 当单例对象为null时进入同步代码块
			synchronized (HssfExcelHelper.class) {
				// 再次判断单例对象是否为null，防止多线程访问时多次生成对象
				if (instance == null) {
					instance = new HssfExcelHelper(file);
				}
			}
		} else {
			// 如果操作的文件对象不同，则重置文件对象
			if (!file.equals(instance.getFile())) {
				instance.setFile(file);
			}
		}
		return instance;
	}

	/**
	 * 获取单例对象并进行初始化
	 * 
	 * @param filePath
	 *            文件路径
	 * @return 返回初始化后的单例对象
	 */
	public static HssfExcelHelper getInstance(String filePath) {
		return getInstance(new File(filePath));
	}

	@Override
	public <T> List<T> readExcel(Class<T> clazz, String[] fieldNames,
			int sheetNo, boolean hasTitle, boolean checkTitles, String[] titles) throws Exception {
		List<T> dataModels = new ArrayList<T>();
		boolean isRowEmpty = false;
		// 获取excel工作簿
		HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(file));
		HSSFSheet sheet = workbook.getSheetAt(sheetNo);
		if(hasTitle && checkTitles) {
			HSSFRow header = sheet.getRow(0);
			if(header == null) {
				return new ArrayList<T>();
			}
			for (int j = 0; j < titles.length; j++) {
				HSSFCell cell = header.getCell(j);
				if (cell == null) {
					continue;
				}
				String content = cell.getStringCellValue();
				if(!StringUtils.trim(content).equalsIgnoreCase(titles[j])) {
					System.out.println("表头不正确，请按照以下栏位：" + Arrays.toString(titles));
					return new ArrayList<T>();
				}
			}
		}
		int start = sheet.getFirstRowNum() + (hasTitle ? 1 : 0); // 如果有标题则从第二行开始
		
		for (int i = start; i <= sheet.getLastRowNum(); i++) {
			HSSFRow row = sheet.getRow(i);
			//begin:is empty row ?
			if (row == null) {
				sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
				i--;
				continue;
			}
			for (int j = 0; j < row.getLastCellNum(); j++) {
				if (row.getCell(j) == null || row.getCell(j).toString().trim().equals("")) {
					isRowEmpty = true;
				} else {
					isRowEmpty = false;
					break;
				}
			}
			if (isRowEmpty == true) {
				sheet.removeRow(row);
				i--;
				continue;
			}
			//after:is empty row ?
			// 生成实例并通过反射调用setter方法
			T target = clazz.newInstance();
			for (int j = 0; j < fieldNames.length; j++) {
				String fieldName = fieldNames[j];
				if (fieldName == null || UID.equals(fieldName)) {
					continue; // 过滤serialVersionUID属性
				}
				// 获取excel单元格的内容
				HSSFCell cell = row.getCell(j);
				if (cell == null) {
					continue;
				}
				String content = cell.getStringCellValue();
				// 如果属性是日期类型则将内容转换成日期对象
				if (isDateType(clazz, fieldName)) {
					// 如果属性是日期类型则将内容转换成日期对象
					ReflectUtil.invokeSetter(target, fieldName,
							format.parse(content));
				} else {
					Field field = clazz.getDeclaredField(fieldName);
					ReflectUtil.invokeSetter(target, fieldName,
							parseValueWithType(content, field.getType()));
				}
			}
			dataModels.add(target);
		}
		return dataModels;
	}

	@Override
	public <T> void writeExcel(Class<T> clazz, List<T> dataModels,
			String[] fieldNames, String[] titles, String sheetNameOrSheetIndex, boolean isCopyHeaderFormat) throws Exception {
		HSSFWorkbook workbook = null;
		HSSFSheet sheet = null;
		// 检测文件是否存在，如果存在则修改文件，否则创建文件
		if (file.exists()) {
			FileInputStream fis = new FileInputStream(file);
			workbook = new HSSFWorkbook(fis);
			if(sheetNameOrSheetIndex != null) {
				if(StringUtils.isNumeric(sheetNameOrSheetIndex)) {
					sheet = workbook.getSheetAt(Integer.parseInt(sheetNameOrSheetIndex));
				} else {
					sheet = workbook.getSheet(sheetNameOrSheetIndex);
				}
			} else {
				//getSheet(0) defaultly
				sheet = workbook.getSheetAt(0);
			}
		} else {
			workbook = new HSSFWorkbook();
			sheet = workbook.createSheet(sheetNameOrSheetIndex);
		}
		Map<Integer, HSSFCellStyle> cellStyleMapping = new HashMap<>();
		// 添加表格标题
		if(titles != null) {//沿用原表头
			HSSFRow headRow = sheet.createRow(0);
			for (int i = 0; i < titles.length; i++) {
				HSSFCell cell = headRow.createCell(i);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(titles[i]);
				// 设置字体加粗
				HSSFCellStyle cellStyle = workbook.createCellStyle();
				HSSFFont font = workbook.createFont();
				font.setBoldweight(Font.BOLDWEIGHT_BOLD);
				cellStyle.setFont(font);
				// 设置自动换行
				cellStyle.setWrapText(true);
				cell.setCellStyle(cellStyle);
				// 设置单元格宽度
				sheet.setColumnWidth(i, titles[i].length() * 1000);
			}
		} else {
			//获取表头格式
			if(fieldNames != null && isCopyHeaderFormat) {
				for(int i=0; i<fieldNames.length; i++) {
					HSSFCell _cell = sheet.getRow(0).getCell(i);
					//表头跟属性列表不一致将会错位
					if(_cell != null) {
						cellStyleMapping.put(i, _cell.getCellStyle());
					}
				}
			}
		}
		// 添加表格内容
		for (int i = 0; i < dataModels.size(); i++) {
			HSSFRow row = sheet.createRow(i + 1);
			// 遍历属性列表
			for (int j = 0; j < fieldNames.length; j++) {
				// 通过反射获取属性的值域
				String fieldName = fieldNames[j];
				if (fieldName == null || UID.equals(fieldName)) {
					continue; // 过滤serialVersionUID属性
				}
				Object result = ReflectUtil.invokeGetter(dataModels.get(i),
						fieldName);
				//System.out.println(dataModels.get(i));
				//System.out.println(fieldName);
				HSSFCell cell = row.createCell(j);
				cell.setCellValue(result.toString());
				cell.setCellStyle(cellStyleMapping.get(j));
				// 如果是日期类型则进行格式化处理
				if (isDateType(clazz, fieldName)) {
					cell.setCellValue(format.format((Date) result));
				}
			}
		}
		// 将数据写到磁盘上
		FileOutputStream fos = new FileOutputStream(file);
		try {
			workbook.write(new FileOutputStream(file));
		} finally {
			if (fos != null) {
				fos.close(); // 不管是否有异常发生都关闭文件输出流
			}
		}
	}
}
